**************************
***GRAPH - STOCK MARKET***
**************************

***Panel A - SP1500 and Dow Jones - Return after keeping the stock for one year

*SP1500
import excel "${original}thomreut_returns.xlsx", sheet("sp1500") firstrow clear

rename SP1500SUPERCOMPOSITEPRICE sp1500

gen year = year(Name)
gen month = month(Name)

gen date = ym(year, month) 
format date %tm

keep year month date sp

sort year month
gen sp_change = (sp-sp[_n-1])/sp[_n-1]

save sp1500.dta, replace

*Dow Jones
import excel "${original}thomreut_returns.xlsx", sheet("Dow Jones") firstrow clear

rename USDOWJONESINDUSTRIALSSHAREP dj

gen year = year(Name)
gen month = month(Name)

gen date = ym(year, month) 
format date %tm

keep if year>=1994
keep year month date dj

sort year month
gen dj_change = (dj-dj[_n-1])/dj[_n-1]

save dowjones.dta, replace

merge 1:1 month year date using sp1500.dta, force
sort year month
save sp_dj.dta, replace


preserve
keep if (year >=1998 & year<=2014)
sort year month

gen sp_1yreturn = (sp1500 - sp1500[_n-12])/sp1500[_n-12]
gen dj_1yreturn = (dj - dj[_n-12])/dj[_n-12]

twoway (connected sp_1yreturn date, lpattern(solid) msymbol(none))(connected dj_1yreturn date, lpattern(longdash) msymbol(none)), ///
title("A. S&P1500 and Dow Jones price indices") subtitle("Return after one year") ytitle("") xtitle("") ///
legend(order(1 "S&P 1500" 2 "Dow Jones")) ///
graphregion(fcolor(white)) ///
ylabel(-0.50 "-50%" -0.40 "-40%" -0.30 "-30%" -0.20 "-20%" -0.10 "-10%" 0 "0%" 0.10 "10%" 0.20 "20%" 0.30 "30%" 0.40 "40%" 0.50 "50%" , angle(0) ) tlabel(, format(%tmYY))
cap graph export FigA1_A.eps, replace

restore


***Panel B - Long-term government and private bonds

*Government bonds
import excel "${original}thomreut_returns.xlsx", sheet("gov bond") firstrow clear

rename USYIELD10YEARGOVERNMENTBOND gov_bond

gen year = year(Name)
gen month = month(Name)

gen date = ym(year, month) 
format date %tm

keep year month date gov_bond
keep if (year>=1994 & year<=2014)

save gov_bonds.dta, replace

*Private corporates bonds
import excel "${original}thomreut_returns.xlsx", sheet("corp bond") cellrange(A3:F5859) firstrow clear

rename DATATYPE DATE
rename YieldToWorst yield

gen year = year(DATE)
gen month = month(DATE)

bys year month: egen corp_bond = mean(yield)

keep year month corp_bond

duplicates drop

gen date = ym(year, month) 
format date %tm 

keep if year<=2014
save corp_bonds.dta, replace

merge 1:1 year month date using gov_bonds.dta, force
save bonds.dta, replace

preserve
keep if (year >=1998 & year<=2014)
sort year month
twoway (connected gov_bond date, lpattern(solid) msymbol(none))(connected corp_bond date, lpattern(longdash) msymbol(none)), ///
title("B. Long term bonds yield") ytitle("") xtitle("") legend(order(1 "10 years government" 2 "7-10 years corporate")) ///
graphregion(fcolor(white)) tlabel(, format(%tmYY)) ///
ylabel(1 "1%" 2 "2%" 3 "3%" 4 "4%" 5 "5%" 6 "6%" 7 "7%" 8 "8%" 9 "9%" 10 "10%", angle(0))
cap graph export FigA1_B.eps, replace
restore


***Panel C - T-Bills and 3-month CDs (TR)

*T-Bills
import excel "${original}thomreut_returns.xlsx", sheet("t-bill") firstrow clear

rename USTREASURYBILLRATE3MONTH tbill

gen year = year(Name)
gen month = month(Name)

gen date = ym(year, month) 
format date %tm

keep year month date tbill
keep if (year>=1994 & year<=2014)

save tbills.dta, replace


*Interest rate on 3-month CDs
import excel "${original}thomreut_returns.xlsx", sheet("cds") firstrow clear

rename USIROFTHE90DAYDEPOSITCERT cd

gen year = year(Name)
gen month = month(Name)

gen date = ym(year, month) 
format date %tm

keep year month date cd
keep if (year>=1994 & year<=2014)

save cd_intrate.dta, replace

merge 1:1 year month date using tbills.dta, force
save cd_tbills.dta, replace


preserve
keep if (year >=1998 & year<=2014)
twoway (connected tbill date, lpattern(solid) msymbol(none))(connected cd date, lpattern(longdash) msymbol(none)), ///
title("C. T-bills and 3-month CDs interest rates") ytitle("") xtitle("") legend(order(1 "T-bills" 2 "CDs")) ///
graphregion(fcolor(white)) tlabel(, format(%tmYY)) ///
ylabel(0 "0%" 1 "1%" 2 "2%" 3 "3%" 4 "4%" 5 "5%" 6 "6%" 7 "7%", angle(0))
cap graph export FigA1_C.eps, replace
restore


***Panel D - 24-month consumer credit interest rate at commercial banks (TR)
import excel "${original}thomreut_returns.xlsx", sheet("debt_nogap") firstrow clear

rename USINTERESTRATESATCOMMERCIAL comm

gen year = year(Name)
gen month = month(Name)

gen date = ym(year, month) 
format date %tm

preserve
keep if (year >=1998 & year<=2014)
twoway (connected comm date, lpattern(solid) msymbol(none)), title("D. Consumer credit interest rate") ytitle("") xtitle("") ///
graphregion(fcolor(white))  tlabel(, format(%tmYY)) ///
ylabel(9 "9%" 10 "10%" 11 "11%" 12 "12%" 13 "13%" 14 "14%" 15 "15%", angle(0)) ///
legend(order(1 "24-month personal loans (commercial banks)") on)
cap graph export FigA1_D.eps, replace
restore

*Erase temporary datasets
erase sp1500.dta
erase dowjones.dta
erase sp_dj.dta
erase gov_bonds.dta
erase corp_bonds.dta
erase bonds.dta
erase tbills.dta
erase cd_intrate.dta
erase cd_tbills.dta
